﻿using AutoMapper;
using Microsoft.AspNetCore.Mvc;
using NET6.WebAPI.Extensions;
using NET6.WebAPI.Entities.User;
using NET6.WebAPI.Reqs.Base;
using NET6.WebAPI.Reqs.User;
using SqlSugar;
using NET6.WebAPI.Reqs.Tool;
using NET6.WebAPI.Enums;
using NET6.WebAPI.Dtos.Tool;
using NET6.WebAPI.Controllers.Base;
using static NET6.WebAPI.Entities.Base.BusinessBase;

namespace NET6.WebAPI.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class UserOfSqlSugarController : BusinessController
    {
        public UserOfSqlSugarController(SqlSugarClient db, IMapper mapper) : base(db, mapper) { }

        [HttpPost]
        public async Task<IActionResult> List(PagingReq<Sys_UserQueryReq> req)
        {
            var pageData = await _db.Queryable<Sys_User>()
                .Where(it => it.RecordStatus != RecordStatusEnum.Deleted)
                .WhereIF(req.Body.UserName.HasValue(), it => it.UserName.Contains(req.Body.UserName.Trim()))
                .OrderBy(it => it.RecordStatus)
                .OrderBy(it => it.CreateTime, OrderByType.Desc)
                //.OrderBy("CreateTime desc")
                //.OrderBy(it => SqlFunc.Desc(it.CreateTime))
                .GetPageListAsync(req.PageNumber, req.PageSize);

            return Ok(new HttpResponseDto(HttpResponseCode.QuerySuccess, pageData));
        }

        [HttpPost]
        public async Task<IActionResult> JoinList(PagingReq<Sys_UserQueryReq> req)
        {
            // 5 个表以上使用 JoinQueryInfos

            var pageData = await _db.Queryable<Sys_User>()
                .InnerJoin<Sys_UserLog>((u, l) => l.UserID == u.ID)
                .Where(u => u.RecordStatus != RecordStatusEnum.Deleted)
                .WhereIF(req.Body.UserName.HasValue(), u => u.UserName.Contains(req.Body.UserName.Trim()))
                .OrderBy(u => u.RecordStatus)
                .OrderBy(u => u.CreateTime, OrderByType.Desc)
                .Select((u, l) => new 
                {
                    u.ID,
                    u.UserName,
                    u.Password,
                    u.RecordStatus,
                    u.CreateTime,
                    l.Info
                })
                .GetPageListAsync(req.PageNumber, req.PageSize);

            return Ok(new HttpResponseDto(HttpResponseCode.QuerySuccess, pageData));
        }

        [HttpPost]
        public async Task<IActionResult> JoinListOfSql(PagingReq<Sys_UserQueryReq> req)
        {
            string querySql = $@"
                select u.*, l.Info from Sys_User u 
                inner join Sys_UserLog l on l.UserID = u.ID 
                where u.RecordStatus != {RecordStatusEnum.Deleted.ToInt()}";

            var pageData = await _db.SqlQueryable<dynamic>(querySql)
                // 使用参数化防 SQL 注入, 目前只能想到这样拼接实现模糊查询, 直接写是行不通的
                .WhereIF(req.Body.UserName.HasValue(), 
                    "UserName like CONCAT('%', @UserName, '%')", new { UserName = req.Body.UserName.Trim() }) 
                .OrderBy("RecordStatus, CreateTime desc")
                .GetPageListAsync(req.PageNumber, req.PageSize);

            return Ok(new HttpResponseDto(HttpResponseCode.QuerySuccess, pageData));
        }

        [HttpPost]
        public async Task<IActionResult> Add(Sys_UserAddReq req)
        {
            await _db.Queryable<Sys_User>().CheckExistsAsync(it => it.UserName == req.UserName);

            int id = 0;
            var user = _mapper.Map<Sys_User>(req);

            try
            {
                await _db.BeginTranAsync();

                id = await _db.Insertable(user).ExecuteReturnIdentityAsync();

                await _db.Insertable(new Sys_UserLog
                {
                    UserID = id,
                    CreateTime = DateTime.Now,
                    Info = "SqlSugar 事务添加用户"
                }).ExecuteCommandAsync();

                await _db.CommitTranAsync();
            }
            catch (Exception ex)
            {
                await _db.RollbackTranAsync();

                return Ok(new HttpResponseDto(HttpResponseCode.InsertFail, ex.Message));
            }

            return Ok(new HttpResponseDto(HttpResponseCode.InsertSuccess, id));
        }

        [HttpPost]
        public async Task<IActionResult> Update(Sys_UserUpdateReq req)
        {
            var user = await _db.Queryable<Sys_User>().GetFirstAsync(it => it.ID == req.ID);

            _mapper.Map(req, user);

            if (await _db.Updateable(user).ExecuteCommandHasChangeAsync())
            {
                return Ok(new HttpResponseDto(HttpResponseCode.ModifySuccess));
            }

            return Ok(new HttpResponseDto(HttpResponseCode.ModifyFail));
        }

        [HttpPost]
        public async Task<IActionResult> Delete(PrimaryKeyReq req)
        {
            await _db.Queryable<Sys_User>().GetFirstAsync(it => it.ID == req.ID);

            if (await _db.Updateable<Sys_User>().LogicalDeleteAsync(req.ID))
            {
                return Ok(new HttpResponseDto(HttpResponseCode.DeleteSuccess));
            }

            return Ok(new HttpResponseDto(HttpResponseCode.DeleteFail));
        }
    }
}
